/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import OBJECT.NhanVien;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.Global;

/**
 *
 * @author TAN
 */
public class NhanVienDAO {
     private Connect connect=null;

     public NhanVienDAO(){}
     
   public NhanVienDAO(String User ,String Pass,String port,String DataBase,String Host) {
        connect =new Connect( User , Pass, port, DataBase, Host);
    }
   
   public String DangNhap(String userName, String password){
       CallableStatement cals = null;
       Connection conn = null;
       ResultSet rs = null;
       String err = "";
       try {
            conn = connect.GetConnect();
            cals = conn.prepareCall("{call sp_DangNhap(?,?,?,?)}");
            cals.setString("Username", userName);
            cals.setString("Password", password);
            cals.setInt("Type", 1);
            cals.registerOutParameter("err", Types.NVARCHAR);
            cals.setString("err", err);
            rs = cals.executeQuery();
            while(rs.next()){
                Global.employee = new NhanVien();
                Global.employee.setManv(rs.getInt("Ma_NV"));
                Global.employee.setTen(rs.getString("Ten_NV"));
                Global.employee.setDiaChi(rs.getString("DiaChi"));
                Global.employee.setDienThoai(rs.getString("DienThoai"));
                Global.employee.setLuong(rs.getFloat("Luong"));
                Global.employee.setLoaiNhanVien(rs.getInt("Loai_NV"));
                Global.employee.setUserName(userName);
                Global.employee.setPassWord(password);
                Global.employee.setTinhTrang(rs.getString("TinhTrang"));
            }
       } catch (Exception e) {
           err = e.getMessage();
           System.err.println(err);
       }finally{
            try {
                err = cals.getString("err");
                if(rs != null){
                    rs.close();
                }   
                cals.close();
                CloseConnect();
            } catch (SQLException ex) {
                System.err.println(ex.getMessage());
            }
       }
       return err;
   }
   
    public String ThemNhanVien(NhanVien nv)
    {   String Loi="";
        Connection conn=connect.GetConnect();
        try {
            CallableStatement cals=conn.prepareCall("{call sp_ThemNhanVien(?,?,?,?,?,?,?,?)}");
            cals.setString(1, nv.getTen());
             cals.setString(2, nv.getDiaChi());
              cals.setString(3, nv.getDienThoai());
               cals.setFloat(4, nv.getLuong());
               cals.setInt(5, nv.getLoaiNhanVien());
               cals.setString(6, nv.getUserName());
               cals.setString(7, nv.getPassWord());
               cals.registerOutParameter(8, java.sql.Types.NVARCHAR);
               cals.execute();
               Loi=cals.getString(8);
                  
        } catch (SQLException ex) {
            Loi="Falied";
            
        }
       finally
        {
            CloseConnect();
        }
        return Loi;
    }
    public boolean SuaNhanVien(NhanVien nv)
    {
        Connection conn=connect.GetConnect();
        try {
            CallableStatement cals=conn.prepareCall("{call sp_SuaNhanVien(?,?,?,?,?,?,?,?,?)}");
            cals.setInt(1, nv.getManv());
            cals.setString(2, nv.getTen());
             cals.setString(3, nv.getDiaChi());
              cals.setString(4, nv.getDienThoai());
               cals.setFloat(5, nv.getLuong());
               cals.setInt(6, nv.getLoaiNhanVien());
               cals.setString(7, nv.getUserName());
               cals.setString(8, nv.getPassWord());
               cals.setString(9, nv.getTinhTrang());
               
               cals.execute();
               
        } catch (SQLException ex) {
            return false;
            
        }
        finally{
        CloseConnect();
        }
       
        return true;
    }
      public String XoaNhanVien(int manv)
    {
        String Message="";
        Connection conn=connect.GetConnect();
        try {
            CallableStatement cals=conn.prepareCall("{call sp_XoaNhanVien(?,?)}");
            cals.setInt(1, manv);
            cals.setString(2, "");
            cals.registerOutParameter(2, java.sql.Types.NVARCHAR);
            
            cals.execute();
               Message=cals.getString(2);
                 
               
               
        } catch (SQLException ex) {
          Message="Faild";
            System.out.println(ex.getMessage());
        }
        finally{
            CloseConnect();
        }
       return Message;
        
    }
      
      public List<NhanVien> LayDanhSachNhanVien(){
          List<NhanVien> ds = new ArrayList<NhanVien>();
          Connection conn = null;
          try {
//              conn = util.Connection.getConnection();
              conn = connect.GetConnect();
          } catch (Exception e) {
              e.printStackTrace();
          }
          
          ResultSet result = null;
          CallableStatement cals = null;
          try {
              int dem = 0;
              cals = conn.prepareCall("{call sp_ThongTinNhanVien(?)}");
              cals.setInt(1, dem);
              result  = cals.executeQuery();
              NhanVien nv = null;
              while(result.next()){
                    nv = new NhanVien();
                    nv.setDiaChi(result.getString("DiaChi"));
                    nv.setDienThoai(result.getString("DienThoai"));
                    nv.setLuong(result.getFloat("Luong"));
                    nv.setLoaiNhanVien(result.getInt("Loai_NV"));
                    nv.setManv(result.getInt("Ma_NV"));
                    nv.setTen(result.getString("Ten_NV"));
                    nv.setTinhTrang(result.getString("TinhTrang"));
                    nv.setPassWord(result.getString("PassWord"));
                    if(!nv.getTinhTrang().equals("2")){
                        ds.add(nv);
                    }
              }
          } catch (Exception e) {
              System.err.println(e.getMessage());
          }finally{
              try {
                  if(result != null){
                      result.close();
                  }
                  cals.close();
                  CloseConnect();
              } catch (Exception e) {
              }
              
          }
          return ds;
      }
      
       public CallableStatement DanhSachNhanVien()
    {
        //ArrayList<NhanVien> ds=new ArrayList<NhanVien>();
        
        Connection conn=connect.GetConnect();
        ResultSet result=null;
        CallableStatement cals=null;
        try {
             cals=conn.prepareCall("{call sp_ThongTinNhanVien(?)}");
              return cals;
            //cals.registerOutParameter(1, java.sql.Types.INTEGER);
            
           // result =cals.executeQuery();
            //Soluong=""+cals.getInt(1);
            //System.out.println(Soluong);
//           while(result.next())
//           {
//               NhanVien nv=new NhanVien();
//               nv.setDiaChi(result.getString("DiaChi"));
//               nv.setDienThoai(result.getString("DienThoai"));
//               nv.setLuong(result.getFloat("Luong"));
//               nv.setLoaiNhanVien(result.getInt("Loai_NV"));
//               nv.setManv(result.getInt("Ma_NV"));
//               nv.setTen(result.getString("Ten_NV"));
//               nv.setTinhTrang(result.getString("TinhTrang"));
//               nv.setPassWord(result.getString("PassWord"));
//               if(!nv.getTinhTrang().equals("2"))
//               ds.add(nv);
//           }
              
        } catch (SQLException ex) {
          ;
            
        }
       return cals;
        //return  ds;
    } 
      public NhanVien Search1NhanVien(int manv)
      {
          NhanVien nv=new NhanVien();
          Connection conn=connect.GetConnect();
          try
          {
              CallableStatement cals=conn.prepareCall("{call ThongTin1NhanVien(?)}");
              cals.setInt(1, manv);
              ResultSet rs=cals.executeQuery();
              if(rs.next())
              {
              nv.setDiaChi(rs.getString("DiaChi"));
              nv.setDienThoai(rs.getString("DienThoai"));
              nv.setLoaiNhanVien(rs.getInt("Loai_NV"));
              nv.setLuong(rs.getFloat("Luong"));
              nv.setManv(rs.getInt("Ma_NV"));
              nv.setPassWord(rs.getString("PassWord"));
              nv.setTen(rs.getString("Ten_NV"));
              nv.setTinhTrang(rs.getString("TinhTrang"));
              nv.setUserName(rs.getString("UserName"));
              }
              rs.close();
          }catch(Exception e)
          {
              return null;
          }
          finally{
              CloseConnect();
          }
          return nv;
      }
      public void CloseConnect()
      {
          try
          {
          connect.Close();
          }catch(Exception e)
          {
              ;
          }
      }
      
}
